/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @author Figueroa
 */

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class ConectorBD {
    
    
    private Connection connection;
    
    
    public void conectar(){
                                   
    try {
    Class.forName("com.mysql.jdbc.Driver").newInstance();
        //connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1/TeChess?user=root&password=", null);
       connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1/TeChess?user=root&password=abc123");
    } catch (Exception connectException) {
      connectException.printStackTrace();
    }
    
    }
    public void consulta(){
    try {
      Statement statement = connection.createStatement();
      ResultSet rs = statement.executeQuery("SHOW TABLES");

      while (rs.next()) {
        //System.out.println(rs.getString(1));
      }
      rs.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    
    }
    
    public List<String> get_niveles(){
    List<String> niveles = new ArrayList();
    try {
      Statement statement = connection.createStatement();
       statement.execute("call Get_Levels()");
      ResultSet rs=statement.getResultSet();
      
      while (rs.next()) {
        niveles.add(rs.getString("name"));
        //System.out.println(rs.getString(1));
      }
      rs.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return niveles;
    }
    
    
    
    public int Create_exercise(String level, String name,String description,String position, int start){
    int id = 0;
    
    try {
      Statement statement = connection.createStatement();
       statement.execute("call Create_Excercise('"+level+"','"+name+"','"+description+"','"+position+"','"+start+"')");
      ResultSet rs=statement.getResultSet();
      
      while (rs.next()) {
        
      id= rs.getInt("id");
      }
      rs.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    
    
    return id;
    
    }
    
    
    
      public int Create_Solution(int id, String solucion){

    try {
      Statement statement = connection.createStatement();
       statement.execute("call Create_Solution('"+id+"','"+solucion+"')");
       
  
    } catch (SQLException e) {
        e.printStackTrace();
    }
    
    
    return id;
    
    }
      
      
   public List<String> Get_Excercices_name (String Level){
   int id = 0;
   String name = null;
   List<String> resp = new ArrayList<String>();
    
    try {
      Statement statement = connection.createStatement();
       statement.execute("call Get_excercises('"+Level+"')");
       ResultSet rs=statement.getResultSet();
      
      while (rs.next()) {
      id = rs.getInt("idExcercises");
      name = rs.getString("name");
      resp.add(id +"|"+name);
      }
      rs.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    
   
    
    
    return resp;
    
   
   
   }
   
    public List<String> Get_ExcercicesByID (int id){
    List<String> resp = new ArrayList<String>();
    try {
      Statement statement = connection.createStatement();
       statement.execute("call Get_ExcerciseByID('"+id+"')");
      ResultSet rs=statement.getResultSet();
      
      while (rs.next()) {
      resp.add(rs.getString("name"));
      resp.add(rs.getString("description"));
      resp.add(rs.getString("position"));
      resp.add(rs.getString("start"));
      }
      rs.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    
    return resp;
    
   
   
   }
   public List<String> GET_SolutionByID(int id){
    List<String> resp = new ArrayList<String>();
    try {
      Statement statement = connection.createStatement();
       statement.execute("call Get_SolutionByID('"+id+"')");
      ResultSet rs=statement.getResultSet();
      
      while (rs.next()) {
      resp.add(rs.getString("solution"));
      }
      rs.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return resp;
   
   }
   
   /*Create_Stat` (id_ejercicio int(11), usuario varchar(45), duracion time, 
                                        resultado tinyint, respuesta text)*/
   public void Create_stat(int ExID, String user, String time, int resul, String answer){
     int resp = 0;
    try {
      Statement statement = connection.createStatement();
     // System.out.println("call Create_stat('"+ExID+"','"+user+"','"+time+"','"+resul+"','"+answer+"')"); 
      statement.execute("call Create_stat('"+ExID+"','"+user+"','"+time+"','"+resul+"','"+answer+"')");
    } catch (SQLException e) {
        e.printStackTrace();
    }
   
 
   }
   
   
   public void Delete_Excercise(int ExID){
     try {
      Statement statement = connection.createStatement();
     // System.out.println("call Create_stat('"+ExID+"','"+user+"','"+time+"','"+resul+"','"+answer+"')"); 
      statement.execute("call Delete_Excercise('"+ExID+"')");
    } catch (SQLException e) {
        e.printStackTrace();
    }
   }


}
